package com.iis.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;

import com.iis.databaseutil.DatabaseConnection;
import com.iis.entity.Agent;
import com.iis.entity.Client;
import com.iis.entity.Person;

public class ClientDAO {
	
	private static final ClientDAO clientDAO = new ClientDAO();
	
	private ClientDAO(){
		
	}
	
	public static ClientDAO getInstances(){
		return clientDAO;
	}
	
	Connection koneksiKu = null;

	PreparedStatement psInsertClient = null;
	
	String sqlInsertClient = "INSERT INTO Client(Registration_No, Category, Region, Person_idPerson) "
			+ "VALUES (?,?,?,?) ";

	public Client save(Client newClient) throws SQLException {
		if (newClient == null) {
			return null;
		}
		koneksiKu = DatabaseConnection.getDataConnection(false);
		int index = 1;
		try {
			
			Person newPerson = newClient;
			
			PersonDAO myPersonDAO = PersonDAO.getInstance();
			
			newPerson = myPersonDAO.save(newPerson);

			psInsertClient = koneksiKu.prepareStatement(sqlInsertClient,
					Statement.RETURN_GENERATED_KEYS);
			
			//generate registration no
			Calendar myCal = Calendar.getInstance();
			myCal.setTime(new java.util.Date());
			String newRegistNo = myCal.YEAR + myCal.MONTH + newPerson.getPersonID() + "";
			newClient.setRegistrationNo(newRegistNo);
			
			
			psInsertClient.setString(index++, newClient.getRegistrationNo());
			// mengubah dari java.util.date ke java.sql.date
			/*
			java.sql.Date sqlRegistrationDate = new java.sql.Date(newClient
					.getRegistrationDate().getTime());
			psInsertClient.setDate(index++, sqlRegistrationDate);*/
			psInsertClient.setString(index++, newClient.getCategory());
			psInsertClient.setString(index++, newClient.getRegion());
			psInsertClient.setLong(index++,	 newClient.getPersonID());
			int jumlahRow = psInsertClient.executeUpdate();
			if (jumlahRow == 0) {
				throw new SQLException("Gagal menyimpan person");
			}
			try (ResultSet generatedKeys = psInsertClient.getGeneratedKeys()) {
				if (generatedKeys.next()) {
					newClient.setIdClient(generatedKeys.getLong(1));
					koneksiKu.commit();
					return newClient;
				} else {
					throw new SQLException("Gagal menyimpan person");
				}
			} catch (Exception e) {
				koneksiKu.rollback();
			}
			

		} catch (SQLException e) {
			e.printStackTrace();
			koneksiKu.rollback();
		}
		return null;
	}
	
	PreparedStatement psSelectAllClient = null;
	String sqlGetAllClient = "SELECT * FROM client s, person p WHERE s.person_idPerson = p.idPerson AND s.deleted = 'N' AND p.deleted = 'N'";
	
	public ArrayList<Client> getAll(){
		koneksiKu = DatabaseConnection.getDataConnection();
		int index = 1;
		ArrayList<Client> arrClient = new ArrayList<Client>();
		try {

			psSelectAllClient = koneksiKu.prepareStatement(sqlGetAllClient);
			ResultSet rs = psSelectAllClient.executeQuery();

			while (rs.next()) {
				String registrationNo = rs.getString("registration_No");
				String name = rs.getString("name");
				String category = rs.getString("category");
				
				Client client = new Client();
				client.setRegistrationNo(registrationNo);
				client.setName(name);
				client.setCategory(category);
				arrClient.add(client);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return arrClient;
	}

}
